﻿Imports System.Data
Imports System.Data.OleDb
Imports DTO
Imports DAO.DataProvider
Public Class DanhSachKhamBenhDAO
    Private _query As String
    ' neu bang co 1 dong >> lay thong tin benh nhan do len
    Public Function HS_Load() As DanhSachKhamBenh_DTO
        Dim DTP As New DataProvider()
        _query = " select * from DANHSACHKHAMBENH "
        Dim _HS As New DanhSachKhamBenh_DTO()
        Dim _cnn As OleDbConnection = DTP.ConnectDB()
        _cnn.Open()
        Dim _dt As New DataTable()
        _dt = DTP.Datatable_PHONGMACHTU(_query)
        _cnn.Close()
        If (_dt.Rows.Count = 1) Then
            Try
                _HS.MaBenhNhan = Long.Parse(_dt.Rows(1)(1))
                _HS.HoTen = _dt.Rows(1)(2).ToString()
                _HS.GioiTinh = _dt.Rows(1)(3).ToString()
                _HS.NamSinh = Date.Parse(_dt.Rows(1)(4))
                _HS.DiaChi = (_dt.Rows(1)(5)).ToString()
                _HS.NgayKham = Date.Parse(_dt.Rows(1)(6))
            Catch ex As Exception

            End Try


        End If
        Return _HS
    End Function
    'them
    Public Function INSERT(ByVal DS As DanhSachKhamBenh_DTO) As Integer
        ' Dim query As String
        Dim DTP As New DataProvider()
        'tao ket noi
        Dim _cnn As OleDbConnection = DTP.ConnectDB()
        'mo ket noi
        _cnn.Open()
        'tao cau lenh command
        'SqlCommand(cmd = New SqlCommand(query, cnn))
        Dim cmd As OleDbCommand = New OleDbCommand()
        cmd.Connection = _cnn
        cmd.CommandText = "insert into DANHSACHKHAMBENH(HoTen,NamSinh,NgayKham,GioiTinh,DiaChi) values  (@HoTen,@NgaySinh,@NgayKham,@GioiTinh,@DiaChi)"
        Dim para As OleDbParameter = cmd.Parameters.Add("@HoTen", OleDbType.VarChar)
        para.Value = DS.HoTen

        para = cmd.Parameters.Add("@NgaySinh", OleDbType.Date)
        para.Value = DS.NamSinh

        para = cmd.Parameters.Add("@NgayKham", OleDbType.Date)
        para.Value = DS.NgayKham

        para = cmd.Parameters.Add("@GioiTinh", OleDbType.VarChar)
        para.Value = DS.GioiTinh

        para = cmd.Parameters.Add("@DiaChi", OleDbType.VarChar)
        para.Value = DS.DiaChi


        'goi thuc thi cau lenh
        Dim flag As Integer = 0

        flag = cmd.ExecuteNonQuery()
        _cnn.Close()
        If (flag > 0) Then
            Return 1
        Else
            Return 0
        End If
        'MessageBox.Show("a" + NV.MaHocSinh)

    End Function
    Public Function UPDATE(ByVal DS As DanhSachKhamBenh_DTO) As Boolean
        Dim DTP As New DataProvider()
        'tao ket noi
        Dim _cnn As OleDbConnection = DTP.ConnectDB()
        'mo ket noi
        _cnn.Open()
        'tao cau lenh command
        'SqlCommand(cmd = New SqlCommand(query, cnn))
        Dim cmd As OleDbCommand = New OleDbCommand()
        cmd.Connection = _cnn
        cmd.CommandText = "update DANHSACHKHAMBENH set (HoTen=@HoTen,NgaySinh=@NgaySinh,NgayKham=@NgayKham,GioiTinh=@GioiTinh,DiaChi=@DiaChi) "
        Dim para As OleDbParameter = cmd.Parameters.Add("@HoTen", OleDbType.BigInt)
        para.Value = DS.HoTen

        para = cmd.Parameters.Add("@NgaySinh", OleDbType.VarWChar)
        para.Value = DS.NamSinh

        para = cmd.Parameters.Add("@NgayKham", OleDbType.VarWChar)
        para.Value = DS.NgayKham

        para = cmd.Parameters.Add("@GioiTinh", OleDbType.Double)
        para.Value = DS.GioiTinh

        para = cmd.Parameters.Add("@DiaChi", OleDbType.Double)
        para.Value = DS.DiaChi
        'goi thuc thi cau lenh

        'goi thuc thi cau lenh
        Dim flag As Integer = 0

        flag = cmd.ExecuteNonQuery()
        _cnn.Close()
        If (flag > 0) Then
            Return 1
        Else
            Return 0
        End If

    End Function
    Public Function DELETE(ByVal Ma As Long) As Boolean
        Dim DTP As New DataProvider()
        Dim _cnn As OleDbConnection = DTP.ConnectDB()
        'mo ket noi
        _cnn.Open()
        'tao cau lenh command
        'SqlCommand(cmd = New SqlCommand(query, cnn))
        Dim cmd As OleDbCommand = New OleDbCommand()
        cmd.Connection = _cnn
        cmd.CommandText = "delete from HocSinh where MaHocSinh=@MA"
        ' Dim query As String = 
        Dim para As OleDbParameter = cmd.Parameters.Add("@MA", OleDbType.BigInt)
        para.Value = Ma
        Dim flag As Integer

        flag = cmd.ExecuteNonQuery()

        If (flag > 0) Then
            Return 1
        Else
            Return 0
        End If

    End Function
    'xu ly lay so luong benh nhan trong bang ds_kham benh
    Public Function SoLuong(ByVal ds_khambenh As DanhSachKhamBenh_DTO) As Integer
        Dim DTP As New DataProvider()
        Dim cnn As New OleDbConnection()
        cnn = DTP.ConnectDB()
        cnn.Open()
        Dim cmd As OleDbCommand = New OleDbCommand()
        cmd.Connection = cnn
        cmd.CommandText = "select count(*) from DanhSachKhamBenh where NgayKham=@ngaykham "
        Dim para As OleDbParameter
        para = cmd.Parameters.Add("@ngaykham", OleDbType.Date)
        para.Value = ds_khambenh.NgayKham
        Dim temp As Integer
        temp = cmd.ExecuteScalar()
        Return temp
    End Function
End Class
